import requests
import xlrd
import json
import xlwt
import pymysql
import threading

def import_data():
    wb = xlrd.open_workbook('D:/PyProject/SinoMed/Test1.xls')
    ws = wb.sheets()[0]
    nrows = ws.nrows
    # print(nrows)
    company_names = ws.col_values(1, 1, nrows)
    company_ids = ws.col_values(2, 1, nrows)
    exchanges = ws.col_values(3, 1, nrows)
    return company_names, company_ids, exchanges

def get_info(url):
    r = requests.get(url)
    #print(r.text)
    if len(r.text) > 200:
        data2 = json.loads(r.text).get('data')
        #print(data2)
        for data3 in data2:
            print(data3)
            read_content(data3)

def read_content(data3):
    #names = locals()
    #print(names)
    keys = []
    values = []
    for key, value in data3.items():
        if "'" in str(value):
            value = str(value).replace("'", "")
        elif "None" in str(value):
            value = str(value).replace("None", "")
        keys.append(key)
        values.append(value)

    """
    for key, value in data2.items():
        #print(key)
        names[str(key)] = value
        # exec('var{}={}'.format(key, key))
    for key, value in names.items():
        #print(str(key) + "=" + str(value))
        if "'" in str(value):
            value = str(value).replace("'", "")
        elif "None" in str(value):
            value = str(value).replace("None", "")
        keys.append(key)
        values.append(value)
    keys = keys[1:]
    values = values[1:]
    """
    print(keys)
    print(values)
    write_in_mysql(keys, values)

def write_in_mysql(keys, values):

    #将第一个字段数值添加进表格，keys[0] #证券码, 将第六个字段数值添加进表格，keys[5] #报告日期
    sql = "INSERT INTO %s (%s, %s) VALUES('%s', '%s')" % (table_name, keys[0], keys[5], values[0], values[5])
    run_sql(sql)
    print(sql)

    #根据第一个字段和第五个字段数值判断行数，循环update之后所有的字段数值, 循环列表values
    for i, value in enumerate(values):
        if i>0:
            sql = "UPDATE %s SET %s='%s' WHERE SECUCODE='%s' AND REPORT_DATE='%s'" % (table_name, keys[i], values[i], values[0], values[5])
            run_sql(sql)
            print(sql)
    print("Done")

def run_sql(sql):
    try:
        cursor.execute(sql)
        db.commit()
    except Exception as e:
        db.rollback()
        raise (e)

def initialize_table(table_name):
    # 此处手动建一个表格，确保表格中有一个字段即可#
    print(table_name)
    sql = "CREATE TABLE %s (Initial int(2))" % (table_name)
    print(sql)
    run_sql(sql)

    url_temp = "http://emweb.securities.eastmoney.com/PC_HSF10/NewFinanceAnalysis/zcfzbAjaxNew?companyType=4&reportDateType=1&reportType=1&dates=2013-12-31&code=SH603963"
    r = requests.get(url_temp)
    data1 = json.loads(r.text).get('data')[0]

    keys = []
    values = []
    print(data1)
    for key, value in data1.items():
        #print(key, value)
        keys.append(key)
        values.append(value)

    # 将所有字段名添加到表格里，循环列表keys
    for i, value in enumerate(values):
        print(i)
        sql = "ALTER TABLE %s ADD " % (table_name) + str(keys[i]) + " TEXT"
        print(sql)
        run_sql(sql)

    """
    date_str = ""
    date_strs = []
    #鉴于数据按日期5位5位读取，将所有日期字符串按5分组，生成日期字符串组date_strs
    for i, date in enumerate(dates):
        date_str += date + "%2C"
        if (i+1)%5 == 0:
            date_strs.append(date_str)
            date_str = ""
        if i+1 == len(dates):
            date_strs.append(date_str)
    print(date_strs)

    url_temps = []
    for date_str in date_strs:
        url_temp = "https://emweb.securities.eastmoney.com/PC_HSF10/NewFinanceAnalysis/zcfzbAjaxNew?companyType=4&reportDateType=0&reportType=1&dates=" + date_str + "&code=SH603963"
        print(url_temp)
        url_temps.append(url_temp)
        r = requests.get(url_temp)
        data1 = json.loads(r.text).get('data')[0]
        print(data1)

        names = locals()
        print(names)
        keys = []
        values = []
        for key, value in data1.items():
            # print(key)
            names[str(key)] = value
            # exec('var{}={}'.format(key, key))
        for key, value in names.items():
            # print(str(key) + "=" + str(value))
            if "'" in str(value):
                value = str(value).replace("'", "")
            elif "None" in str(value):
                value = str(value).replace("None", "")
            keys.append(key)
            values.append(value)
        keys = keys[15:]
        values = values[15:]
        print(keys)
        print(values)
        print(len(values))
    # 将所有字段名添加到表格里，循环列表keys
    for i, value in enumerate(values):
        print(i)
        sql = "ALTER TABLE %s ADD " % (table_name) + str(keys[i]) + " TEXT"
        print(sql)
        run_sql(sql)
    """

def date_list(i, company_id):
    dates = []
    url = "https://emweb.securities.eastmoney.com/PC_HSF10/NewFinanceAnalysis/zcfzbDateAjaxNew?companyType=4&reportDateType=0&code=" + exchanges[i][:2] + company_id
    r = requests.get(url)
    #print(r.text)
    data = json.loads(r.text).get('data')
    for datum in data:
        date = datum.get('REPORT_DATE')[:10]
        #print(date)
        dates.append(date)
    return dates

def sort_date(dates):
    date_str = ""
    date_strs = []
    # 鉴于数据按日期5位5位读取，将所有日期字符串按5分组，生成日期字符串组date_strs
    for i, date in enumerate(dates):
        date_str += date + "%2C"
        if (i + 1) % 5 == 0:
            date_strs.append(date_str)
            date_str = ""
        if i + 1 == len(dates):
            date_strs.append(date_str)
    #print(date_strs)
    return date_strs

class myThreading(threading.Thread):
    def __init__(self, i, company_id):
        threading.Thread.__init__(self)
        self.i = i
        self.company_id = company_id
        print(self.name)

    def run(self):

        dates = date_list(self.i, self.company_id)
        #print(dates)
        date_strs = sort_date(dates)

        for date_str in date_strs:
            lock.acquire()
            url = "http://emweb.securities.eastmoney.com/PC_HSF10/NewFinanceAnalysis/zcfzbAjaxNew?companyType=4&reportDateType=1&reportType=1&dates=" + date_str + "&code=" + exchanges[self.i][0:2] + str(self.company_id)
            print(url)
            get_info(url)
            lock.release()

if __name__ == '__main__':
    db = pymysql.connect(host='localhost',
                         user='root',
                         password='aa115541',
                         database='SINOMED')
    cursor = db.cursor()
    company_names, company_ids, exchanges = import_data()
    #table_name = input("输入数据所在表格名称:")
    table_name = "test2"

    threads = []
    lock = threading.Lock()
    initialize_table(table_name)

    for i, company_id in enumerate(company_ids):
        #url = "http://emweb.securities.eastmoney.com/PC_HSF10/NewFinanceAnalysis/ZYZBAjaxNew?type=0&code=SH603963"
        #print(url)
        thread = myThreading(i, company_id)
        threads.append(thread)

    for thread in threads:
        thread.start()
        print(thread.name)
        #thread.join()